---
title: "Spring Data JPA Result Mapping"
date: 2020-6-25
categories:
- java
- spring-jpa
tags:
---

<div id="content">
<div id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#orgaabf358">基础</a></li>
<li><a href="#org99e58e3">构造函数映射</a></li>
<li><a href="#org017d668">映射到实体</a></li>
</ul>
</div>
</div>
<blockquote>
<p>
<a href="https://www.baeldung.com/jpa-sql-resultset-mapping">https://www.baeldung.com/jpa-sql-resultset-mapping</a>
<a href="https://stackoverflow.com/questions/25188939/how-to-map-the-result-set-of-a-jpa-nativequery-to-a-pojo-using-sqlresultsetmappi">https://stackoverflow.com/questions/25188939/how-to-map-the-result-set-of-a-jpa-nativequery-to-a-pojo-using-sqlresultsetmappi</a>
<a href="https://thorben-janssen.com/result-set-mapping-complex-mappings/">https://thorben-janssen.com/result-set-mapping-complex-mappings/</a>
</p>
</blockquote>
<p>
查询的结果不是@Entity的实体、有sum,max等聚合的结果、native query、table join，执行这些语句的时候就会需要自定义映射。
</p>
<p>
ResultMapping还有一个好处是能利用内置的和自定义的Converter.
</p>
<div class="outline-2" id="outline-container-orgaabf358">
<h2 id="orgaabf358">基础</h2>
<div class="outline-text-2" id="text-orgaabf358">
<p>
注意 @SqlResultSetMapping 需要加在 @Entity 的实体类上，否则会报 mapping not found
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">@SqlResultSetMapping</span>(
  name=<span style="font-style: italic;">"FridayEmployeeResult"</span>,
  columns={<span style="font-weight: bold; text-decoration: underline;">@ColumnResult</span>(name=<span style="font-style: italic;">"employeeId"</span>)})

<span style="font-weight: bold; text-decoration: underline;">@NamedNativeQuery</span>(
  name = <span style="font-style: italic;">"FridayEmployees"</span>,
  query = <span style="font-style: italic;">"SELECT employeeId FROM schedule_days WHERE dayOfWeek = 'FRIDAY'"</span>,
  resultSetMapping = <span style="font-style: italic;">"FridayEmployeeResult"</span>)
</pre>
</div>
<p>
这块注解加在@Entity注解的实体类上，但不是说这个Mapping将结果集转换成那个实体类型，只是为了让Jpa能找到这个名为 {% raw %} FridayEmployeeResult {% endraw %} 的映射。
</p>
<p>
ColumnResult还有另一个参数type，能够指定类型，这样就能使用自定义和内置的Converter
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">List</span>&lt;<span style="font-weight: bold; text-decoration: underline;">Long</span>&gt; <span style="font-weight: bold; font-style: italic;">ds</span> = entityManager.createNamedQuery(<span style="font-style: italic;">"FridayEmployees"</span>).getResultList();
</pre>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-org99e58e3">
<h2 id="org99e58e3">构造函数映射</h2>
<div class="outline-text-2" id="text-org99e58e3">
<p>
定义一个类（非实体类）用于接收结果集
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">@AllArgsConstructor</span>
<span style="font-weight: bold;">public</span> <span style="font-weight: bold; text-decoration: underline;">ScheduledDay</span> (
  <span style="font-weight: bold; text-decoration: underline;">Long</span> <span style="font-weight: bold; font-style: italic;">id</span>;
  <span style="font-weight: bold; text-decoration: underline;">Long</span> <span style="font-weight: bold; font-style: italic;">employeeId</span>; 
  <span style="font-weight: bold; text-decoration: underline;">String</span> <span style="font-weight: bold; font-style: italic;">dayofWeek</span>;
}
</pre>
</div>
<p>
在某个实体类上定义映射
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">@SqlResultSetMapping</span>(
    name=<span style="font-style: italic;">"ScheduleResult"</span>,
    classes={
      <span style="font-weight: bold; text-decoration: underline;">@ConstructorResult</span>(
        targetClass=<span style="font-weight: bold; text-decoration: underline;">com</span>.<span style="font-weight: bold; text-decoration: underline;">baeldung</span>.<span style="font-weight: bold; text-decoration: underline;">sqlresultsetmapping</span>.ScheduledDay.<span style="font-weight: bold;">class</span>, <span style="font-weight: bold; font-style: italic;">// </span><span style="font-weight: bold; font-style: italic;">结果集类型</span>
        columns={ <span style="font-weight: bold; font-style: italic;">// </span><span style="font-weight: bold; font-style: italic;">与构造函数对应</span>
          <span style="font-weight: bold; text-decoration: underline;">@ColumnResult</span>(name=<span style="font-style: italic;">"id"</span>, type=Long.<span style="font-weight: bold;">class</span>),
          <span style="font-weight: bold; text-decoration: underline;">@ColumnResult</span>(name=<span style="font-style: italic;">"employeeId"</span>, type=Long.<span style="font-weight: bold;">class</span>),
          <span style="font-weight: bold; text-decoration: underline;">@ColumnResult</span>(name=<span style="font-style: italic;">"dayOfWeek"</span>)})})
</pre>
</div>
<p>
查询：
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">List</span> <span style="font-weight: bold; font-style: italic;">query</span> = entity.createNativeQuery(<span style="font-style: italic;">"select id as id, employee_id as employeeId, day_of_week as dayOfWeek from scheduled_day"</span>, <span style="font-style: italic;">"ScheduleResult"</span>);
<span style="font-weight: bold; font-style: italic;">// </span><span style="font-weight: bold; font-style: italic;">query 是 ScheduledDay 类型的集合</span>
</pre>
</div>
<p>
sql语句中的select自由编写，能包含sum, max等，但是select列的别名和顺序需要与SqlResultSetMapping中定义的一致
Note: 上面的例子中只是选择了列的子集，如果仅仅是为了实现这个，只需要使用JPA的Projection。
</p>
</div>
</div>
<div class="outline-2" id="outline-container-org017d668">
<h2 id="org017d668">映射到实体</h2>
<div class="outline-text-2" id="text-org017d668">
<p>
join的情况下，一次返回两个表的结果
在某个实体上定义映射
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">@SqlResultSetMappings</span>(
        <span style="font-weight: bold;">@SqlResultSetMapping</span>(
                name = <span style="font-style: italic;">"SkuWithSpuResultMap"</span>,
                entities = {
                        <span style="font-weight: bold; text-decoration: underline;">@EntityResult</span>(entityClass = SKU.<span style="font-weight: bold;">class</span>),
                        <span style="font-weight: bold; text-decoration: underline;">@EntityResult</span>(entityClass = SPU.<span style="font-weight: bold;">class</span>)
                }
        )
)
</pre>
</div>
<p>
调用：
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">String</span> <span style="font-weight: bold; font-style: italic;">sql</span> = <span style="font-style: italic;">"select a.id, a.name,... b.id, b.name... from sku a join spu b on a.spu_id=b.id"</span>;
<span style="font-weight: bold; text-decoration: underline;">var</span> <span style="font-weight: bold; font-style: italic;">query</span> = entityManager.createNativeQuery(sql, <span style="font-style: italic;">"SkuWithSpuResultMap"</span>);
<span style="font-weight: bold; text-decoration: underline;">List</span> <span style="font-weight: bold; font-style: italic;">ds</span> = query.getResultList();
<span style="font-weight: bold;">for</span> (<span style="font-weight: bold; text-decoration: underline;">var</span> <span style="font-weight: bold; font-style: italic;">row</span> : ds) {
    <span style="font-weight: bold; font-style: italic;">// </span><span style="font-weight: bold; font-style: italic;">row 是一个数组，但已经转化为实体</span>
    <span style="font-weight: bold; text-decoration: underline;">SKU</span> <span style="font-weight: bold; font-style: italic;">sku</span> = row[0];
    <span style="font-weight: bold; text-decoration: underline;">SPU</span> <span style="font-weight: bold; font-style: italic;">spu</span> = row[1];
    ...
}
</pre>
</div>
</div>
</div>
</div>
<div class="status" id="postamble">
<p class="date">Date: 2020-6-25</p>
<p class="author">Author: gdme1320</p>
<p class="validation"><a href="http://validator.w3.org/check?uri=referer">Validate</a></p>
</div>
